﻿Imports DevExpress.XtraReports.UserDesigner
Imports System.ComponentModel.Design
'OutStandingIssue: Should Go in WinFormExt/Form - aleblanc - 2011-12-19
Public Class ReportBuilder
    Public Property dataset As DataSet


    Private activeReport As XRDesignPanel
    Public Property aMyXtraReport As MyXtraReport

    Public Property ChooseCommandEnable As Boolean = True
    Public Property ChooseReportEnable As Boolean = True
    Public Property NewReportEnable As Boolean = True

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub ReportBuilder_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If aMyXtraReport IsNot Nothing Then
            Dim mq As MetaQuery
            OpenDocument(aMyXtraReport.DocumentTemplateName)
            '
            'OUSTANDINGISSUE: il manque de loader le FieldList selon le aMyXtraReport.QueryID_FK (qui a ete setter auparavant. voir ItemMasterControl.vb line 1235)
            '1- Get QueryName selon le aMyXtraReport.QueryID_FK
            '2- LoadFieldList(queryName)
            If aMyXtraReport.QueryID_FK <> Guid.Empty Then
                mq = MetaQuery.GetMetaQueryById(aMyXtraReport.QueryID_FK)
                If Not String.IsNullOrEmpty(mq.QueryName) Then
                    LoadFieldList(mq.QueryName)
                End If
            End If
            '
            AddDocumenTemplate_ToReportTag(activeReport.Report, aMyXtraReport.DocumentTemplateID, activeReport.Report.Name, aMyXtraReport.QueryID_FK)
        End If

        Me.MetaCommand.Enabled = ChooseCommandEnable
        Me.btnOpen.Enabled = ChooseReportEnable
        Me.btnNewReport.Enabled = NewReportEnable
    End Sub


    ''' <summary>
    ''' This is to handle the 'Save in DataBase'
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnSave_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles btnSave.ItemClick
        Save()
    End Sub

    ''' <summary>
    ''' This is to save the report in the DataBase
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub Save()
        Me.Cursor = Cursors.WaitCursor
        activeReport = Me.XrDesignMdiController1.ActiveDesignPanel

        'OUTSTANDINGISSUE: aurait du etre fait tout de suite apres avoir cliquer sur New Report mais je nai pas trouver le bon event. clieng 2013-04-29.
        If activeReport IsNot Nothing AndAlso TryCast(activeReport.Report.Tag, MyXtraReport) Is Nothing Then
            AddDocumenTemplate_ToReportTag(activeReport.Report, Guid.NewGuid, activeReport.Report.Name, Nothing)
        End If


        Dim aMyReport As MyXtraReport = DirectCast(activeReport.Report.Tag, MyXtraReport)
        RptNotification.SaveReportInDB(activeReport.Report, aMyReport.DocumentTemplateID, aMyReport.CategoryID, aMyReport.TypeID, aMyReport.ClassID, aMyReport.DataFormatID, aMyReport.QueryID_FK)

        activeReport.ReportState = ReportState.Saved
        Me.Cursor = Cursors.Default
    End Sub

    Private Sub LoadData_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles LoadData.ItemClick
        Dim aDataset As DataSet = New DataSet()
        Dim sr As System.IO.StringReader
        Dim mq As MetaQuery
        Dim sqlcmd As System.Data.SqlClient.SqlCommand = Nothing
        Dim aParamFrm As MqParamForm
        Dim aExecQuery As DialogResult = DialogResult.OK

        activeReport = Me.XrDesignMdiController1.ActiveDesignPanel
        If activeReport IsNot Nothing Then
            Me.Cursor = Cursors.WaitCursor

            sr = New System.IO.StringReader(activeReport.Report.DataSourceSchema)
            aDataset.ReadXmlSchema(sr)
            mq = MetaQuery.GetMetaQueryByName(aDataset.DataSetName)

            If mq.MetaParams.Rows.Count > 0 Then
                aParamFrm = New MqParamForm(mq.QueryName)
                aExecQuery = aParamFrm.ShowDialog()

                'On dit OK pour la form de Parametre
                If aExecQuery = DialogResult.OK Then
                    sqlcmd = mq.InitQueryParams

                    'On prend les parametre défini par la MQ, pour les retrouver dans le sqlCmd.Parameters
                    'Les metaColumns on la priorité
                    Dim param As SqlClient.SqlParameter
                    For Each row As DataRow In mq.MetaParams.Rows
                        If sqlcmd.Parameters.Contains(row("ColumnName").ToString) Then
                            param = sqlcmd.Parameters(row("ColumnName").ToString)
                            If param IsNot Nothing AndAlso Not IsDBNull(aParamFrm.SelectedParam(row("ColumnName").ToString)) Then
                                param.Value = aParamFrm.SelectedParam(row("ColumnName").ToString)
                            End If
                        Else
                            MessageWindow.ShowMessage(New Exception(String.Format("Report Builder Load, SQL Parameter not found: {0}", row("ColumnName"))))
                        End If
                    Next
                End If
                mq.FillResultQuery(sqlCmd:=sqlcmd)

                aDataset = New DataSet()
                aDataset.Tables.Add(mq.ResultQuery)

                If mq.GetChildDisplayQuery.Rows.Count > 0 Then


                    Dim strListOfParentGUID As String = String.Format("'{0}',", Guid.Empty)
                    For Each aRow As DataRow In mq.ResultQuery.Rows
                        strListOfParentGUID = String.Format("{0}'{1}',", strListOfParentGUID, aRow(mq.PKColumn_Name))
                    Next

                    For Each row As DataRow In mq.GetChildDisplayQuery.Rows
                        Using mq2 As MetaQuery = MetaQuery.GetMetaQueryByName(row("QueryName").ToString)
                            mq2.SQLWhere = String.Format("{0} in ({1})", mq2.FKColumn_Name, Mid(strListOfParentGUID, 1, Len(strListOfParentGUID) - 1))
                            mq2.FillResultQuery()
                            aDataset.Tables.Add(mq2.ResultQuery)

                            Dim aRelation As New DataRelation(row("Relation").ToString, mq.ResultQuery.Columns(row("PKColumn_Name").ToString), mq2.ResultQuery.Columns(row("FKColumn_Name").ToString))
                            aDataset.Relations.Add(aRelation)
                            '
                        End Using
                    Next
                End If
            Else
                mq.FillResultQuery()

                aDataset = New DataSet()
                aDataset.Tables.Add(mq.ResultQuery)

                For Each row As DataRow In mq.GetChildDisplayQuery.Rows
                    Using mq2 As MetaQuery = MetaQuery.GetMetaQueryByName(row("QueryName").ToString)
                        mq2.FillResultQuery()
                        aDataset.Tables.Add(mq2.ResultQuery)

                        Dim aRelation As New DataRelation(row("Relation").ToString, mq.ResultQuery.Columns(row("PKColumn_Name").ToString), mq2.ResultQuery.Columns(row("FKColumn_Name").ToString))
                        aDataset.Relations.Add(aRelation)
                        '
                    End Using
                Next
            End If
            'activeReport.Report.DataSource = mq.ResultQuery




            activeReport.Report.DataSource = aDataset
            sr.Dispose()
            Me.Cursor = Cursors.Default
        End If
    End Sub

    Private Sub SchemaRefresh_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles SchemaRefresh.ItemClick
        Dim dataset As DataSet = New DataSet()
        Dim sr As System.IO.StringReader
        activeReport = Me.XrDesignMdiController1.ActiveDesignPanel
        If activeReport IsNot Nothing Then
            If Not String.IsNullOrEmpty(activeReport.Report.DataSourceSchema) Then
                sr = New System.IO.StringReader(activeReport.Report.DataSourceSchema)
                dataset.ReadXmlSchema(sr)
                'http://documentation.devexpress.com/#XtraReports/CustomDocument6620
                'How to: Programmatically Refresh the Field List
                Dim fieldList As FieldListDockPanel = CType(XrDesignDockManager1(DesignDockPanelType.FieldList), FieldListDockPanel)
                activeReport.Report.DataSourceSchema = Nothing
                Dim host As IDesignerHost = CType(activeReport.GetService(GetType(IDesignerHost)), IDesignerHost)
                fieldList.UpdateDataSource(host)
                activeReport.Report.DataSourceSchema = GetSchemaCommand(dataset.DataSetName)
                host = CType(activeReport.GetService(GetType(IDesignerHost)), IDesignerHost)
                fieldList.UpdateDataSource(host)
                'Fin How to: Programmatically Refresh the Field List
                fieldList.Show()
                fieldList.Focus()

                activeReport.ReportState = ReportState.Changed
                sr.Dispose()
            Else
                MessageWindow.ShowMessage("Invalid Query, Query Not Found", MsgBoxStyle.Information)
            End If
        End If
    End Sub

    Private Sub MetaCommand_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles MetaCommand.ItemClick
        Dim mqChooser As MqChooser = New MqChooser()
        activeReport = Me.XrDesignMdiController1.ActiveDesignPanel
        '
        If activeReport IsNot Nothing Then
            mqChooser.Mode = ChooserMode.MetaCommand
            If mqChooser.ShowDialog() = Windows.Forms.DialogResult.OK Then
                '
                LoadFieldList(mqChooser.QueryName)
                '
                If TryCast(activeReport.Report.Tag, MyXtraReport) IsNot Nothing Then
                    DirectCast(activeReport.Report.Tag, MyXtraReport).QueryID_FK = mqChooser.QueryID
                Else
                    AddDocumenTemplate_ToReportTag(activeReport.Report, Guid.NewGuid, activeReport.Report.Name, mqChooser.QueryID)
                End If

                activeReport.ReportState = ReportState.Changed
            End If
        End If
    End Sub

    Private Function GetSchemaCommand(ByVal queryName As String) As String
        Dim swrt As System.IO.StringWriter
        Using dataset As DataSet = New DataSet()
            Using mq As MetaQuery = MetaQuery.GetMetaQueryByName(queryName)
                Try
                    If mq IsNot Nothing Then
                        swrt = New System.IO.StringWriter()
                        'on veut pas de data juste le schema du resultat
                        mq.FillResultQuery(CommandBehavior.SchemaOnly)
                        'Le nom du Dataset sera le nom de la requete Parent
                        dataset.DataSetName = mq.QueryName
                        dataset.Tables.Add(mq.ResultQuery)
                        'OutStandingIssue: Aller chercher la definition des requete enfants
                        'Voir mq.GetChildDisplayQuery()

                        For Each row As DataRow In mq.GetChildDisplayQuery.Rows
                            Using mq2 As MetaQuery = MetaQuery.GetMetaQueryByName(row("QueryName").ToString)
                                mq2.FillResultQuery(CommandBehavior.SchemaOnly)
                                dataset.Tables.Add(mq2.ResultQuery)

                                Dim aRelation As New DataRelation(row("Relation").ToString, mq.ResultQuery.Columns(row("PKColumn_Name").ToString), mq2.ResultQuery.Columns(row("FKColumn_Name").ToString))
                                dataset.Relations.Add(aRelation)
                                '
                            End Using
                        Next

                        dataset.WriteXmlSchema(swrt)
                        Return swrt.ToString
                    Else
                        Return Nothing
                    End If
                Catch ex As Exception
                    Throw ex
                End Try
            End Using
        End Using

    End Function



    Private Sub btnOpen_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles btnOpen.ItemClick
        Dim mqChooser As MqChooser = New MqChooser()
        '
        mqChooser.Mode = ChooserMode.ReportQuery
        If mqChooser.ShowDialog() = Windows.Forms.DialogResult.OK Then
            If Not String.IsNullOrEmpty(mqChooser.ReportName) Then
                Me.Cursor = Cursors.WaitCursor
                OpenDocument(mqChooser.ReportName)

                AddDocumenTemplate_ToReportTag(activeReport.Report, mqChooser.ReportID, activeReport.Report.Name, mqChooser.QueryID)

                Me.Cursor = Cursors.Default
            End If
        End If
    End Sub

    Private Sub OpenDocument(ByVal reportName As String)
        Dim report As DevExpress.XtraReports.UI.XtraReport
        Me.Cursor = Cursors.WaitCursor
        report = RptNotification.GetReportFromDB(reportName)

        If report IsNot Nothing Then
            Me.XrDesignMdiController1.OpenReport(report)
            activeReport = Me.XrDesignMdiController1.ActiveDesignPanel
        End If
        Me.Cursor = Cursors.Default
    End Sub

    Private Sub LoadFieldList(ByVal queryName As String)
        'http://documentation.devexpress.com/#XtraReports/CustomDocument6620
        'How to: Programmatically Refresh the Field List
        Dim fieldList As FieldListDockPanel = CType(XrDesignDockManager1(DesignDockPanelType.FieldList), FieldListDockPanel)
        activeReport.Report.DataSourceSchema = Nothing
        Dim host As IDesignerHost = CType(activeReport.GetService(GetType(IDesignerHost)), IDesignerHost)
        fieldList.UpdateDataSource(host)
        'OUTSTANDINGISSUE: GetSchemaCommand() does not return fields from the childs Queries
        activeReport.Report.DataSourceSchema = GetSchemaCommand(queryName)
        host = CType(activeReport.GetService(GetType(IDesignerHost)), IDesignerHost)
        fieldList.UpdateDataSource(host)
        'Fin How to: Programmatically Refresh the Field List
        fieldList.Show()
        fieldList.Focus()

    End Sub

    Private Sub AddDocumenTemplate_ToReportTag(ByRef rpt As DevExpress.XtraReports.UI.XtraReport, ByVal documentID As Guid, ByVal documentName As String, ByVal queryID As Guid)
        If rpt IsNot Nothing Then
            Dim aReport As MyXtraReport = New MyXtraReport()
            aReport.DocumentTemplateID = documentID
            aReport.DocumentTemplateName = documentName
            aReport.QueryID_FK = queryID
            rpt.Tag = aReport
        End If
    End Sub

End Class



Public Class MyXtraReport
    Public Property DocumentTemplateID As Guid
    Public Property DocumentTemplateName As String
    Public Property QueryID_FK As Guid
    Public Property QueryName As String
    Public Property CategoryID() As Guid '= Guid.Parse("3ACE3C8F-6010-4E6C-8E72-BC0287A2AD0A") ' Cat = Report
    Public Property TypeID() As Guid '= Guid.Parse("74dc9100-d93f-4cd6-a587-77031950d318") 'Type = Repx (Devex Report Layout)
    Public Property DataFormatID() As Guid '= Guid.Parse("97b21b5e-fd1d-489b-8265-d6018c8341ad") ' Format = UTF8
    Public Property ClassID() As Guid '= Guid.Parse("97b21b5e-fd1d-489b-8265-d6018c8341ad") ' Format = UTF8
    Public Property ControlID_FK As Guid

    Public Sub New()
        MyBase.New()
    End Sub

End Class